package com.sylar.happyreader.db;

import java.util.ArrayList;
import java.util.List;

import com.sylar.happyreader.bean.BookItem;

import android.content.Context;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;
import android.text.TextUtils;
import android.util.Log;

public class Crud {

	private DatabaseHelper dbHelper;

	public Crud(Context ctx) {
		dbHelper = new DatabaseHelper(ctx);
	}

	public void insert(String bookName, String pageNo) {

		Boolean b = checkIsExists(bookName, pageNo);
		if (b)
			return;
		// Log.d("yixs", "insert : bookName " +bookName +"pageNo " +pageNo );
		Object[] params = { bookName, pageNo };
		String sql = "insert into book(bookname,pageno) values(?,?)";
		SQLiteDatabase db = null;
		try {
			db = dbHelper.getWritableDatabase();
			if (db != null) {
				db.beginTransaction();
				db.execSQL(sql, params);
				db.setTransactionSuccessful();
			}
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			db.endTransaction();
			if (db != null)
				db.close();
		}
	}

	public Boolean queryByName(String bookName, String pageNo) {

		return false;
	}

	public void deleteByTime() {

	}

	public Boolean checkIsExists(String bookName, String pageNo) {
		if(TextUtils.isEmpty(bookName)) return false;
		boolean b = false;
		String sql = "select count(1) from book where bookname = ? and pageno = ?";
		String[] params = { bookName, pageNo };
		SQLiteDatabase db = null;
		Cursor cur = null;
		try {
			db = dbHelper.getWritableDatabase();
			if (db != null)
				cur = db.rawQuery(sql, params);
			if (cur != null) {
				if (cur.moveToNext()) {
					cur.moveToFirst();
					int count = cur.getInt(0);
					if (count > 0)
						b = true;
				}
			}
		} catch (Exception e) {
			e.printStackTrace();
			if (cur != null)
				cur.close();
		} finally {
			if (cur != null)
				cur.close();
			if (db != null)
				db.close();
		}

		// Log.d("yixs", "checkIsExists :" + b+"");
		return b;

	}

	public Boolean checkBookHisory(String bookname) {
		boolean b_result = false;
		String sql = "select count(1) from bookhisory where bookname = ?";
		String[] params = { bookname };
		SQLiteDatabase db = null;
		Cursor cur = null;
		try {
			db = dbHelper.getWritableDatabase();
			if (db != null)
				cur = db.rawQuery(sql, params);
			if (cur != null) {
				if (cur.moveToNext()) {
					cur.moveToFirst();
					int count = cur.getInt(0);
					if (count > 0)
						b_result = true;
				}
			}
		} catch (Exception e) {
			e.printStackTrace();
			if (cur != null)
				cur.close();
		} finally {
			if (cur != null)
				cur.close();
			if (db != null)
				db.close();
		}
		return b_result;
	}

	public void insertBookHisory(String bookName, String sex, String style) {

		Boolean b = checkBookHisory(bookName);
		if (b) return;
		 Log.d("yixs", "insertBookHisory : bookName: " +bookName +"sex: " +sex + "style : " + style );
		Object[] params = { sex, style, bookName };
		String sql = "insert into bookhisory(sex,style,bookname) values(?,?,?)";
		SQLiteDatabase db = null;
		try {
			db = dbHelper.getWritableDatabase();
			if (db != null) {
				db.beginTransaction();
				db.execSQL(sql, params);
				db.setTransactionSuccessful();
			}
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			db.endTransaction();
			if (db != null)
				db.close();
		}
	}
	
	public void delBookHisoryCache(){
		String sql = "delete from bookhisory where _id not in(SELECT _id FROM bookhisory order by _id desc LIMIT 0,50);";
		SQLiteDatabase db = null;
		try {
			db = dbHelper.getWritableDatabase();
			if (db != null) {
				db.beginTransaction();
				db.execSQL(sql);
				db.setTransactionSuccessful();
			}
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			db.endTransaction();
			if (db != null)
				db.close();
		}
	}

	public List<BookItem> getBookHisoryList() {
		// delete from (select * from Tab order by time asc) where rownum
		// <max(rownum)) where rownum> 50;
		List<BookItem> books = new ArrayList<BookItem>();
		String sql = "select * from bookhisory order by _id desc";
		SQLiteDatabase db = null;
		Cursor cur = null;
		try {
			db = dbHelper.getWritableDatabase();
			if (db != null)
				cur = db.rawQuery(sql, null);
			while (cur.moveToNext()) {
				BookItem book = new BookItem();
				int personid = cur.getInt(0); 
				String sex = cur.getString(1);
				String style = cur.getString(2);
				String bookname = cur.getString(3);
				book.setSex(sex);
				book.setStyle(style);
				book.setBookname(bookname);
				books.add(book);
			}

		} catch (Exception e) {
			e.printStackTrace();
			books = null;
			if (cur != null)
				cur.close();
		} finally {
			if (cur != null)
				cur.close();
			if (db != null)
				db.close();
		}
		
		return books;
	}
}
